"""
    根据逻辑处理需要，提供数据支持
"""
import pymysql
import hashlib

# 密码加密
def change_passwd(passwd):
    hash=hashlib.sha256()  #其中一种加密算法 sha256
    hash.update(passwd.encode())
    return hash.hexdigest()

class DICT:
    def __init__(self):
        self.kwargs = {
            'host': '192.168.43.88',
            "port": 3306,
            "user": "root",
            "password": "123456",
            "database": "dict",
            "charset": "utf8"
        }
        self.db = pymysql.connect(**self.kwargs)
        self.cur = self.db.cursor()

    # 注册验证
    def register(self,name,password):
        password=change_passwd(password)  #进行加密
        sql="insert into user (name,password) values (%s,%s);"
        try:
            self.cur.execute(sql,[name,password])
            self.db.commit()
            return True
        except Exception as e:
            print(e)
            self.db.rollback()
            return False
    # 处理登录
    def login(self,name,password):
        password=change_passwd(password)  #进行加密
        sql = "select id from user where binary name=%s and password=%s;"
        self.cur.execute(sql,[name,password])
        # (id,)   None
        if self.cur.fetchone():  # 就关心有没有查到
            return True
        else:
            return False
    # 查询单词
    def query(self,word):
        sql= "select mean from words where word=%s;"
        self.cur.execute(sql,[word])
        return self.cur.fetchone()  # (mean,) None
    # 可以在此处做判断，也可以在server处做判断

    def insert_hist(self,name,word):
        # id user_id  words_id  time
        sql="select id from user where name=%s"
        self.cur.execute(sql,[name])
        user_id=self.cur.fetchone()[0]
        sql = "select id from words where word=%s"
        self.cur.execute(sql, [word])
        words_id = self.cur.fetchone()[0]
        sql= "insert into hist (user_id,words_id) values (%s,%s);"
        self.cur.execute(sql,[user_id,words_id])
        self.db.commit()
    def hist(self,name):
        # name  word  time--> user_id words_id time
        sql="select name,word,time " \
            "from user inner join hist " \
            "on user.id = hist.user_id " \
            "inner join words " \
            "on words.id = hist.words_id " \
            "where name= %s " \
            "order by time desc " \
            "limit 10;"
        # sql = "select name,word,time " \
        #       "from user inner join hist " \
        #       "on user.id=hist.user_id " \
        #       "inner join words " \
        #       "on words.id=hist.words_id " \
        #       "where name=%s " \
        #       "order by time desc " \
        #       "limit 10;"
        self.cur.execute(sql,[name])
        return self.cur.fetchall()

    def close(self):
        # 关闭
        self.cur.close()
        self.db.close()

if __name__ == '__main__':
     dict=DICT()  #实例化
     dict.insert_word() #调用方法
     dict.close()
